/**
 * Chesney's Fireplace System Includes New Supplier, New Fireplace Item, Supplier and Fireplace Update, 
 * Supplier and Fireplace Reports, Login, main menu and Tutorial.
 * 
 * It also Includes Database connection, Database Extension and the two classes which are MySupplier and My Fireplace.
 *  
 * Copyright (C) Bournemouth University 
 * 
 * @author Ruth Shepperd, Ari Lopez
 * 
 * @version 1.0
 */
package Database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
* Class Database.
* This Class will be used by other classes to implement its methods to
* operate with a database
* Declared as abstract because it is a superclass, no objects can be created from it.
* For the purpose of this class, Database has four attributes to hold the the username,
* password, dabase path and the connection.
* These attributes can be indirectly set by various means using appropriate methods.
* 
* @author Ari Lopez and Ruth Shepperd
* 
* @version 1.0
* 
*/
public abstract class Database {

	// Attributes
	private static String username, password, dbPath;
	private static Connection connection;

	/**
	 * Set Localhost User name, Password, database path e.g.
	 * "localhost/programming2"
	 * 
	 * @param user
	 *            (String)
	 * @param pass
	 *            (String)
	 * @param path
	 *            (String)
	 */
	public static void initialise(String user, String pass, String path) {
		username = user;
		password = pass;
		dbPath = path;
	}

	/**
	 * Get the connection via Driver Manager This method must be run first in
	 * order connect User name, Password, database path e.g.
	 * "localhost/programming2"
	 * 
	 * @return true or false (as a boolean)
	 */
	public static boolean connect() {
		try {
			connection = DriverManager.getConnection("jdbc:mysql://" + dbPath,
					username, password);

			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Get the connection to commit on the database to save the data
	 * 
	 * @return true or false (as a boolean)
	 */
	public static boolean commit() {
		try {
			connection.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Get the connection to rollback on the database to go back to the previous
	 * commit
	 * 
	 * @return true or false (as a boolean)
	 */
	public static boolean rollback() {
		try {
			connection.rollback();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Get the database connection to close
	 * 
	 * @return true or false (as a boolean)
	 */
	public static boolean close() {
		try {
			connection.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Get the doSQL to execute an SQL statement where nothing is returned
	 * 
	 * @param query
	 *            (String)
	 * @return true or false (as a boolean)
	 */
	public static boolean doSQL(String query) {
		Statement stmt;

		try {
			stmt = connection.createStatement();
			stmt.executeUpdate(query);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Get the return query
	 * 
	 * @param query
	 *            (String)
	 * @return result (as a 2D String array)
	 */
	public static String[][] returnQuery(String query) {

		Database.connect();

		StringBuilder output = new StringBuilder();
		Statement stmt;

		try {
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			int rowCount = 0;

			while (rs.next()) {
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					output.append(rs.getString(i) + "~");
				}
				output.append("\n");
				rowCount++;
			}

			String[][] result = new String[rs.getMetaData().getColumnCount()][rowCount];

			String s[] = output.toString().split("~");

			int i = 0;
			for (int y = 0; y < result[0].length; y++) {
				for (int x = 0; x < result.length; x++) {

					result[x][y] = s[i];
					i++;
				}
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Set the printQuery in the form of 2D String array to the console.
	 * 
	 * @param s
	 *            (String)
	 */
	public static void printQuery(String[][] s) {

		for (int y = 0; y < s[0].length; y++) {
			for (int x = 0; x < s.length; x++) {

				System.out.print(s[x][y] + "\t");
			}
			System.out.println();
		}
	}
}